package com.powerunion.datacollection.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import net.sf.json.JSONArray;
import org.apache.log4j.Logger;

import com.powerunion.datacollection.common.Constants;
import com.powerunion.datacollection.common.FileUtils;
import com.powerunion.datacollection.common.Format;
import com.powerunion.datacollection.common.SystemException;
import com.powerunion.datacollection.dao.DaoException;
import com.powerunion.datacollection.dao.DataCollectionFormDao;
import com.powerunion.datacollection.model.Accessories;
import com.powerunion.datacollection.model.DataCollectionForm;
import com.powerunion.datacollection.model.jsonObject.DataCollectionFormJSONObject;
import com.powerunion.datacollection.model.query.DataCollectionFormQuery;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameterSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.db.jdbc.operator.UpdateOperator;
import com.powerunion.util.DBUtils;
import com.powerunion.util.log.LogHelper;

public class DataCollectionFormDaoImpl extends JdbcBase implements DataCollectionFormDao {
	// 初始化日志类
	private static final Logger logger = LogHelper.getInstance().getLogger(
			DataCollectionFormDaoImpl.class); 
	
	public boolean delete(String[] codes) throws DaoException {
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "delete");
		}
		String sql = "update dc_collection_form set IF_DEL = 1 where id = ? and i_use = 0";
		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);
		
		for(int i=0;i<codes.length;i++) {
			SqlParameterSet spSet = new SqlParameterSet();
			spSet.addParameter(new SqlParameter(SqlType.VARCHAR,codes[i]));
			update.addBatchParameter(spSet);
		}
		try{
			update.access();
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("delete throw error!");
		}
		
		int[] effectCount = update.getBatchEffectCounts();
		if(effectCount != null && effectCount.length>0) {
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "表单信息"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("delete:" + Constants.DELETE + "表单信息"
						+ Constants.FAIL);
			}
			return false;
		}
	
	}

	public String save(DataCollectionForm dcf) throws DaoException{ 
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "save");
		}
		String sql = "";
		String resultId = "";
		if (dcf.getId() == null || dcf.getId().equals("")) {//新增
			String id = String.valueOf(this.getSeq("dc_collection_form"));
			dcf.setId(id);
			StringBuffer sqlSave = new StringBuffer();
			sqlSave.append("insert into dc_collection_form (form_name,form_desc,CRT_USER,CRT_DATE,MODI_USER,MODI_DATE,IF_DEL,")
				   .append("form_category_id,id) values(?,?,?,?,?,?,?,?,?)");
			sql = sqlSave.toString();
		} else {//修改
			StringBuffer sqlUpdate = new StringBuffer();
			sqlUpdate.append("update dc_collection_form set form_name =?, form_desc=?,CRT_USER=?,")
				   .append("CRT_DATE=?, MODI_USER =? ,MODI_DATE =?,IF_DEL=?,form_category_id =? where id =?");
			sql = sqlUpdate.toString();
		}

		UpdateOperator update = new UpdateOperator();
		update.setSql(sql);
		update.addParameter(new SqlParameter(SqlType.VARCHAR,dcf.getFormName()));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,dcf.getFormDesc()));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,dcf.getCreateUser()));
		update.addParameter(new SqlParameter(SqlType.DATE,new java.sql.Date(dcf.getCreateDate().getTime())));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,dcf.getModifyUser()));
		update.addParameter(new SqlParameter(SqlType.DATE,new java.sql.Date(dcf.getModifyDate().getTime())));
		update.addParameter(new SqlParameter(SqlType.INTEGER,dcf.isIfDelete()==true?new Integer(1):new Integer(0)));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,dcf.getCategoryId()));
		update.addParameter(new SqlParameter(SqlType.VARCHAR,dcf.getId()));
		try{
			update.access();
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("save throw error!");
		}
		
		int effectCount = update.getEffectCounts();
		if(effectCount > 0) {
			
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "表单信息"
						+ Constants.SUCCESS);
			}
			resultId = dcf.getId();
			return resultId;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + Constants.SAVE + "表单信息"
						+ Constants.FAIL);
			}
			return "";
		}
	
	}
	
	public DataCollectionForm getBykey(String id) throws DaoException{
		DataCollectionForm dcf = new DataCollectionForm();
		StringBuffer sql = new StringBuffer();
		sql.append("select id,form_name,form_desc,CRT_USER,CRT_DATE,MODI_USER,MODI_DATE,")
		   .append("IF_DEL,form_category_id from dc_collection_form where id = ? ");
		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR,id));
		try{
			query.access();	
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("getBykey throw error!");
		}
		
		if(query.resultSetAvailable()) {//检查结果集是否可用
			//获取并处理记录集
			RsDataSet rs = new RsDataSet(query.getSqlResultSet());
			if(rs.rowCount > 0) {
				dcf.setId(rs.getFieldValueAsString("id"));
				dcf.setFormName(rs.getFieldValueAsString("form_name"));
			    dcf.setCreateDate(rs.getFieldValueAsDate("CRT_DATE"));
			    dcf.setCreateUser(rs.getFieldValueAsString("CRT_USER"));
			    dcf.setFormDesc(rs.getFieldValueAsString("form_desc"));
			    dcf.setIfDelete(rs.getFieldValueAsInteger("IF_DEL")== new Integer(1)?true:false);
			    dcf.setModifyDate(rs.getFieldValueAsDate("MODI_DATE"));
			    dcf.setModifyUser(rs.getFieldValueAsString("MODI_USER"));
				dcf.setCategoryId(rs.getFieldValueAsString("form_category_id"));
			}
			rs.clearAll();
		}
		return dcf;
	}
	
	public String getDataCollectionForms(DataCollectionFormQuery query,int start,int limit) {
		logger.debug(Constants.INTOMETHOD + "getDataCollectionForms");
		StringBuffer sqlStr = new StringBuffer();
		sqlStr.append("select id,form_name,form_desc,CRT_USER,CRT_DATE,MODI_USER,MODI_DATE,")
		   .append("IF_DEL,form_category_id from dc_collection_form where form_category_id = ? and IF_DEL = 0");
		
		String sql = sqlStr.toString();

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			//ResultSet.TYPE_SCROLL_INSENSITIVE:可滚动。但是不受其他用户对数据库更改的影响
			//ResultSet.CONCUR_READ_ONLY,只读
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(start+limit);
			ps.setString(1, query.getCategoryId());
			rs = ps.executeQuery();
			//将游标移动到要输出的第一条记录
			rs.first();
			rs.relative(start-1);
			
			List pageList = new ArrayList();
			while(rs.next()) {
				DataCollectionFormJSONObject dfjo = this.getDCF(rs);
				pageList.add(dfjo);
			}

			// 转化成JSON数组
			JSONArray jsonarr = JSONArray.fromObject(pageList);
			StringBuffer resultStr = new StringBuffer();
			int total = this.totalRecord(conn, query.getCategoryId());
			resultStr.append("{total:").append(total).append(",results:")
					.append(jsonarr).append("}");
			logger.debug("getDataCollectionForms:" + Constants.SEARCH + "DataCollectionForms"
					+ Constants.SUCCESS);
			return resultStr.toString();
			
		} catch (Exception e) {
			logger.error("getDataCollectionForms:" + Constants.SEARCH
					+ "DataCollectionForms" + Constants.FAIL,e);
			throw new SystemException("search db record error!");
		}finally{
			DBUtils.close(rs, ps, conn);
		}
	
	}
	
	public final DataCollectionFormJSONObject getDCF(ResultSet rs) throws SQLException {
		DataCollectionFormJSONObject dcfjo = new DataCollectionFormJSONObject();
		dcfjo.setId(rs.getString("id"));
		dcfjo.setFormName(rs.getString("form_name"));
		Date crtDate = rs.getDate("CRT_DATE");
		String crtDateStr = Format.getInstance().dateToYMD(crtDate);
	    dcfjo.setCreateDateStr(crtDateStr);
	    dcfjo.setCreateUser(rs.getString("CRT_USER"));
	    dcfjo.setFormDesc(rs.getString("form_desc"));
	    dcfjo.setIfDelete(rs.getInt("IF_DEL")==1?true:false);
	    Date modiDate = rs.getDate("MODI_DATE");
	    String modiDateStr = Format.getInstance().dateToYMD(modiDate);
	    dcfjo.setModifyDateStr(modiDateStr);
	    dcfjo.setModifyUser(rs.getString("MODI_USER"));
		dcfjo.setCategoryId(rs.getString("form_category_id"));

		return dcfjo;
	}
	
	private int totalAccessories(Connection conn,String actId,String formId)throws DaoException{
		int count=0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(id) from dc_accessories where act_id = ? and form_id=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, actId);
			ps.setString(2, formId);
			rs = ps.executeQuery();
			while(rs.next()){
				count = rs.getInt(1);
			}
			
		}catch(Exception e){

			logger.error("totalAccessories() of throws : ", e);
			throw new DaoException("totalAccessories:" + Constants.SEARCH
					+ "UploadFiles totalRecord" + Constants.FAIL);
		}finally{
			DBUtils.close(rs, ps);
		}
		return count;
	}
	
	private int totalRecord(Connection conn,String categoryId){
		int count=0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			String sql = "select count(id) from dc_collection_form where form_category_id = ? ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, categoryId);
			rs = ps.executeQuery();
			while(rs.next()){
				count = rs.getInt(1);
			}
			
		}catch(Exception e){
			logger.error("totalRecord:" + Constants.SEARCH
					+ "dc_collection_form totalRecord" + Constants.FAIL);
			logger.error("totalRecord() of throws : ", e);
		}finally{
			DBUtils.close(rs, ps);
		}
		return count;
	}
	
	public String getUploadFiles(int start,int limit,String actId,String formId) {
		logger.debug(Constants.INTOMETHOD + "getUploadFiles");
		
		String sql = "";
		if(actId.equals("null")){
			sql = "select id,file_name,file_size,file_type from dc_accessories where act_id is null and form_id="+formId;
		}else{
			sql = "select id,file_name,file_size,file_type from dc_accessories where act_id ="+actId+" and form_id="+formId;
		}

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			//ResultSet.TYPE_SCROLL_INSENSITIVE:可滚动。但是不受其他用户对数据库更改的影响
			//ResultSet.CONCUR_READ_ONLY,只读
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
			ps.setMaxRows(start+limit);
			rs = ps.executeQuery();
			//将游标移动到要输出的第一条记录
			rs.first();
			rs.relative(start-1);
			
			List pageList = new ArrayList();
			while(rs.next()) {
				Accessories accessories = this.getUF(rs);
				pageList.add(accessories);
			}

			// 转化成JSON数组
			JSONArray jsonarr = JSONArray.fromObject(pageList);
			StringBuffer resultStr = new StringBuffer();
			int total = this.totalAccessories(conn, actId,formId);
			resultStr.append("{total:").append(total).append(",results:")
					.append(jsonarr).append("}");
			logger.debug("getUploadFiles:" + Constants.SEARCH + "UploadFiles"
					+ Constants.SUCCESS);
			return resultStr.toString();
			
		} catch (Exception e) {
			logger.error("getUploadFiles:" + Constants.SEARCH
					+ "UploadFiles" + Constants.FAIL,e);
			throw new SystemException("search db record error!");
		}finally{
			DBUtils.close(rs, ps, conn);
		}
	
	}
	
	protected final Accessories getUF(ResultSet rs) throws SQLException {

		Accessories accessories = new Accessories();
		accessories.setId(rs.getString("id"));
		accessories.setFilename(rs.getString("file_name"));
		accessories.setFileSize(rs.getString("file_size"));
		accessories.setFileType(rs.getString("file_type"));

		return accessories;
	}
	
	public String deleteUploadFiles(String path,String[] codes) {
		StringBuffer result = new StringBuffer();
		boolean iflag = false;
		for(int i=0;i<codes.length;i++) {
			String filePath = path+"\\"+codes[i];
			iflag = FileUtils.getInstance().deleteFile(filePath);
			if(!iflag) {
				if(!result.toString().equals("")) {
					result.append(",");
				}
				result.append(codes[i]);
			}
		}
		return result.toString();
	}
	
	

}
